package com.analyst.jdbc;
import com.analyst.entity.Job;

import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class JdbcExample {
    public static void insert( List<Job> jobs) {
        try {
            // 1. 注册驱动类
            Class.forName("com.mysql.jdbc.Driver");
            // 2. 创建连接
            String url = "jdbc:mysql://localhost:3307/job?serverTimezone=UTC&useSSL=false";
            String user = "root";
            String password = "1234";
            Connection conn = DriverManager.getConnection(url, user, password);
            // 3. 创建语句
            Statement stmt = conn.createStatement();


            String sql = "drop table if exists sql_job";

            // 4. 执行查询
            boolean execute = stmt.execute(sql);

            sql="create table sql_job\n" +
                    "(\n" +
                    "\tid int not null auto_increment,\n" +
                    "\t`sqlString` blob null,\n" +
                    "\tbeginTime datetime null,\n" +
                    "\tendTime datetime null,\n" +
                    "\tconstraint sql_job_pk\n" +
                    "\t\tprimary key (id)\n" +
                    ");\n" +
                    "\n";
            stmt.execute(sql);


            sql = "insert into job.sql_task (job_string,  begin_time, end_time) VALUES (?, ? , ?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            for (Job job : jobs) {
//                new ByteArrayInputStream(job.getJobString().toString().getBytes(StandardCharsets.UTF_8))
                Blob blob = conn.createBlob();
                 blob.setBytes(1, job.getSqlString().toString().getBytes(StandardCharsets.UTF_8));
                ps.setBlob(1,blob);
//                ps.setString(1, job.getJobString().toString());

                DateFormat df = new SimpleDateFormat("yy-MM-dd HH:mm:ss");//注意月和小时的格式为两个大写字母
                Date date = job.getBeginTime();//获得开始时间
                String dateStr = df.format(date);//将开始时间转换成特定格式的时间字符串
                ps.setString(2, dateStr);//将时间字符串插入到数据库中
//                ps.setString(2, dateStr);//将时间字符串插入到数据库中
//                ps.setTimestamp(2, new Timestamp(job.getBeginTime().getTime()));;
                 df = new SimpleDateFormat("yy-MM-dd HH:mm:ss");//注意月和小时的格式为两个大写字母
                 date = job.getEndTime();//获得开始时间
                 dateStr = df.format(date);//将开始时间转换成特定格式的时间字符串
                ps.setString(3, dateStr);//将时间字符串插入到数据库中
                ps.addBatch();

            }
            int[] result = ps.executeBatch();
            System.out.println(result);






//            while (rs.next()) {
//                long id = rs.getLong("ID");
//                String name = rs.getString("FIRST_NAME");
//                String lastName = rs.getString("LAST_NAME");
//                // do something with the extracted data...
//            }
            // 5. 关闭连接
//            rs.close();
            stmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            // handle the exception
            System.out.println(e);
        } catch (SQLException e) {
            // handle the exception
            System.out.println(e);
        }
    }

    public static void main(String[] args) {
        insert(null);
    }
}
